package com.hand.excel;

import com.hand.db.Conn;
import com.hand.db.SqlUtil;
import com.hand.ui.MainWindowsComponent;
import com.hand.utils.InfoBuild;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    public static void readByLine(String filePath, Connection conn) throws Exception {
        File file = new File(filePath);
        if (!file.exists()){
            throw new Exception("文件不存在!");
        }
        InputStream in = Files.newInputStream(file.toPath());
        Statement st = conn.createStatement();
        ArrayList<Integer> errorLine1 = new ArrayList<>();
        Map<Integer, String> errorLine2 = new HashMap<>();
        ArrayList<Integer> errorLine3 = new ArrayList<>();

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);

        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            TextArea infoArea = MainWindowsComponent.getInstance().getInfoArea();
            String info = InfoBuild.build(infoArea.getText(), "正在解析Excel：" +i+ "/" + (sheetAt.getPhysicalNumberOfRows()-1));
            infoArea.setText(info);
            String[] values = new String[3];
            XSSFRow row = sheetAt.getRow(i);

            // 读取每一格内容
            try {
                int cellIndex = Integer.parseInt(MainWindowsComponent.getInstance().getInvoiceFlag().getText());
                XSSFCell cell = row.getCell(cellIndex);
                cell.setCellType(CellType.STRING);
                if ("".equals(String.valueOf(cell))) {
                    throw new NullPointerException();
                }
                values[0] = String.valueOf(cell);
            } catch (NullPointerException e) {
                errorLine1.add(i + 1);
                continue;
            }

            try {
                int cellIndex1 = Integer.parseInt(MainWindowsComponent.getInstance().getProduct().getText());
                XSSFCell cell1 = row.getCell(cellIndex1);
                cell1.setCellType(CellType.STRING);
                values[1] = String.valueOf(cell1);
                if ("".equals(String.valueOf(cell1))) {
                    values[1] = "null";
                }
            } catch (NullPointerException e) {
                values[1] = "null";
            }

            try {
                int cellIndex2 = Integer.parseInt(MainWindowsComponent.getInstance().getLine().getText());
                XSSFCell cell2 = row.getCell(cellIndex2);
                cell2.setCellType(CellType.STRING);
                values[2] = String.valueOf(cell2);
                if ("".equals(String.valueOf(cell2))) {
                    values[2] = "null";
                }
            } catch (NullPointerException e) {
                values[2] = "null";
            }


            try {
                boolean isInsert = SqlUtil.insert(st, values);
                if (isInsert) {
                    info = InfoBuild.build(infoArea.getText(), "第"+ i +"行导入成功！");
                    infoArea.setText(info);
                } else {
                    info = InfoBuild.build(infoArea.getText(), "第"+ i +"行没有对应的T2行！");
                    infoArea.setText(info);
                    errorLine3.add(i + 1);
                }
            } catch (Exception e) {
                errorLine2.put(i + 1, e.getMessage());
            }


        }
        TextArea infoArea = MainWindowsComponent.getInstance().getInfoArea();
        String info = InfoBuild.build(infoArea.getText(), "Excel解析完成！");
        infoArea.setText(info);
        info = InfoBuild.build(infoArea.getText(), "失败数据：");
        infoArea.setText(info);
        if (errorLine1.size() > 0) {
            info = InfoBuild.build(infoArea.getText(), "Excel错误：" + errorLine1 + "--发票行标识为空");
            infoArea.setText(info);
        }
        if (errorLine2.size() > 0) {
            info = InfoBuild.build(infoArea.getText(), "SQL错误：" + errorLine2);
            infoArea.setText(info);
        }
        if (errorLine3.size() > 0) {
            info = InfoBuild.build(infoArea.getText(), "Excel错误：" + errorLine3  + "--没有查询到T2行");
            infoArea.setText(info);
        }

    }

}
